package df.bean.guarantee;

import java.sql.SQLException;
import df.bean.db.conn.DBConn;

public class ProcessGuaranteeSummaryBean {
    DBConn cdb;
    String[][] temp_table = null;
    String month = "";
    String year = "";
    String hospital = "";
    String result = "";
    boolean write_comment = false;

    public ProcessGuaranteeSummaryBean(DBConn cdb){
        try {
            this.cdb = cdb;
            if (this.cdb.getStatement() == null) {
                this.cdb.setStatement();
            }
        } catch (SQLException ex) {
            this.result = ""+ex;
        }
    }
    
    public String getMessage(){
        return this.result;
    }
    
    public boolean summaryProcess(String month, String year, String hospital, String process_type){
        boolean status = true;
        try {
            this.month = month;
            this.year = year;
            this.hospital = hospital;
            
            if(process_type.equals("Summary Guarantee Transaction")){
                status = sumAmountGuarantee();
            }
            if(process_type.equals("Summary Guarantee Tax")){
                status = sumTaxGuarantee();
            }
            if(process_type.equals("Summary Guarantee Monthly")){
                status = sumMonthGuarantee();
                status = sumExpenseGuarantee();
            }
        
        } catch (Exception ex) {
            status = false;
        }
        return status;
    }
    
    public boolean sumAmountGuarantee(){
        String[][] tmp = null;
        String temp = "";
        boolean status = true;
        String stm = "";
            //Select from Transaction and Sum Amount of Guarantee Separate by Tax Type, Cash, Credit and Hold
            stm = "SELECT HOSPITAL_CODE, GUARANTEE_DR_CODE, GUARANTEE_CODE, GUARANTEE_TYPE, "+
                  "SUM(CASE WHEN (TAX_TYPE_CODE ='406') THEN GUARANTEE_PAID_AMT ELSE '0' END) AS DF_406_ABSORB, "+
                  "SUM(CASE WHEN (TAX_TYPE_CODE ='402') THEN GUARANTEE_PAID_AMT ELSE '0' END) AS DF_402_ABSORB, "+
                  "SUM(CASE WHEN (TAX_TYPE_CODE ='400') THEN GUARANTEE_PAID_AMT ELSE '0' END) AS DF_400_ABSORB "+

                  "FROM TRN_DAILY "+
                  "WHERE GUARANTEE_TERM_YYYY = '"+this.year+"' AND GUARANTEE_TERM_MM = '"+this.month+"' AND " +
                  "GUARANTEE_NOTE = 'ABSORB SOME GUARANTEE' AND "+ 
                  //comment line below for change calculate absorb 406 to absorb some guarantee only 18/01/2010
                  //"GUARANTEE_NOTE IN ('ABSORB GUARANTEE','ABSORB SOME GUARANTEE') AND "+
                  "HOSPITAL_CODE = '"+this.hospital+"' AND ACTIVE = '1' "+
                  "GROUP BY HOSPITAL_CODE, GUARANTEE_DR_CODE, GUARANTEE_CODE, GUARANTEE_TYPE";
            if(this.write_comment){
                System.out.println(stm);            	
            }
        tmp = cdb.query(stm);
        try {
            for(int i = 0; i<tmp.length; i++){
                //Return Message
                temp = "Update STP_GUARANTEE by GUARANTEE_DR_CODE="+tmp[i][1]+"' AND "+
                       "GUARANTEE_CODE="+tmp[i][2];
                //When Select Guarantee Amount finish
                //Update Guarantee Amount in to Guarantee Setup Table
                stm = "UPDATE STP_GUARANTEE SET "+
                      "DF406_HOLD_AMOUNT = '"+tmp[i][4]+"', "+
                      "DF402_HOLD_AMOUNT = '"+tmp[i][5]+"', "+
                      "DF400_HOLD_AMOUNT = '"+tmp[i][6]+"' "+
                      "WHERE HOSPITAL_CODE = '"+tmp[i][0]+"' AND "+
                      "GUARANTEE_DR_CODE = '"+tmp[i][1]+"' AND "+
                      "GUARANTEE_CODE = '"+tmp[i][2]+"' AND "+
                      "GUARANTEE_TYPE_CODE = '"+tmp[i][3]+"'";
                if(this.write_comment){
                	System.out.println(stm);
                }
                cdb.insert(stm);
            }
            cdb.commitDB();
        } catch (Exception ex) {
            status = false;
            result = "Update calculate guarantee amount error : \n"+ex+
                     "\nCause "+temp;
            cdb.rollDB();
        }
        return status;
    }

    public boolean sumTaxGuarantee(){
        String[][] tmp = null;
        String temp = "";
        boolean status = true;

        String stm = "";
            stm = "SELECT HOSPITAL_CODE, GUARANTEE_DR_CODE, GUARANTEE_CODE, GUARANTEE_TYPE_CODE "+
                  "FROM STP_GUARANTEE "+
                  "WHERE YYYY = '"+this.year+"' AND MM = '"+this.month+"' AND " +
                  //"GUARANTEE_NOTE = 'DF ABSORB' AND "+
                  "HOSPITAL_CODE = '"+this.hospital+"' AND ACTIVE = '1' "+
                  "GROUP BY HOSPITAL_CODE, GUARANTEE_DR_CODE, GUARANTEE_CODE, GUARANTEE_TYPE_CODE";
            if(this.write_comment){
            	System.out.println(stm);
            }
        tmp = cdb.query(stm);

        try{
            for(int i = 0; i<tmp.length; i++){
                temp = "UPDATE STP_GUARANTEE SET "+
                    "SUM_TAX_406 = DF406_HOLD_AMOUNT, "+//absorb df transaction
                    "SUM_TAX_402 = DF402_HOLD_AMOUNT+HP402_ABSORB_AMOUNT+GUARANTEE_EXCLUDE_AMOUNT, "+
                    //absorb df transaction + absorb by hospital + parttime

                    "SUM_TAX_400 = DF400_HOLD_AMOUNT "+
                    "WHERE HOSPITAL_CODE = '"+tmp[i][0]+"' AND "+
                    "GUARANTEE_DR_CODE = '"+tmp[i][1]+"' AND "+
                    "GUARANTEE_CODE = '"+tmp[i][2]+"' AND "+
                    "GUARANTEE_TYPE_CODE = '"+tmp[i][3]+"'";        
                cdb.insert(temp);
            }
            cdb.commitDB();
        } catch (Exception ex) {
            status = false;
            result = "Update calculate guarantee amount error : \n"+ex+
                     "\nCause "+temp;
            cdb.rollDB();
        }
        return status;
    }
    private boolean sumMonthGuarantee(){
        boolean status = true;
        try {
        	cdb.insert(getSummaryGuarantee());
            cdb.commitDB();
        } catch (SQLException ex) {
            cdb.rollDB();
            status = false;
            System.out.println("Update Summary Month Guarantee : "+ex);
            System.out.println("On Script : "+getSummaryGuarantee());
        }
        return status;
    }
    private boolean sumExpenseGuarantee(){
    	String[][] absorb_condition = null;
    	String get_absorb_type = "SELECT CODE, ACCOUNT_CODE, TAX_TYPE_CODE, ABSORB_TYPE FROM EXPENSE " +
    			"WHERE ABSORB_TYPE <> '' AND HOSPITAL_CODE = '"+hospital+"'";
    	absorb_condition = cdb.query(get_absorb_type);
    	if(absorb_condition.length>0){
    		for(int i = 0; i < absorb_condition.length; i++){
    			if(absorb_condition[i][3].equals("HP")){
    				insertExpenseGuaranteeHP(absorb_condition[i][0],absorb_condition[i][1],absorb_condition[i][2]);
    			}else if(absorb_condition[i][3].equals("DR")){
    				insertExpenseGuaranteeDR(absorb_condition[i][0],absorb_condition[i][1],absorb_condition[i][2]);
    			}else{
    				//no condition
    			}
    		}	
    	}
    	return true;
    }

    private String getSummaryGuarantee(){
    	String t = 
        	"INSERT INTO SUMMARY_GUARANTEE " +
            "(HOSPITAL_CODE, DOCTOR_CODE, YYYY, MM, "+
            "GUARANTEE_AMOUNT, GUARANTEE_FIX_AMOUNT, GUARANTEE_INCLUDE_AMOUNT, "+
            "GUARANTEE_EXCLUDE_AMOUNT, SPECIAL_AMOUNT, SUM_HP_OVER_AMOUNT, "+
            "SUM_TAX_406, SUM_TAX_402, SUM_TAX_400) "+
            
            "SELECT HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM, "+
            "GUARANTEE_AMOUNT, GUARANTEE_FIX_AMOUNT, GUARANTEE_INCLUDE_AMOUNT, "+
            "GUARANTEE_EXCLUDE_AMOUNT, SPECIAL_AMOUNT, SUM_HP_OVER_AMOUNT, "+
        	"SUM_TAX_406, SUM_TAX_402, SUM_TAX_400 "+
        	"FROM VW_SUM_GUARANTEE "+ 
        	"WHERE HOSPITAL_CODE = '"+this.hospital+"' AND YYYY = '"+this.year+"' AND MM = '"+this.month+"'"+
        	"ORDER BY GUARANTEE_DR_CODE, YYYY, MM ";
    	return t;
    }

    private boolean insertExpenseGuaranteeDR(String expense_code, String account_code, String tax_type_code){
    	String s = "INSERT INTO TRN_EXPENSE_DETAIL (" +
		"HOSPITAL_CODE, DOCTOR_CODE, DOC_NO," +
		"LINE_NO,DOC_DATE,AMOUNT,TAX_AMOUNT,EXPENSE_SIGN," +
		"EXPENSE_ACCOUNT_CODE,EXPENSE_CODE,TAX_TYPE_CODE," +
		"YYYY,MM,NOTE,USER_ID) " +
    	
		"SELECT HOSPITAL_CODE, DOCTOR_CODE, " +
		"'Absorb Guarantee', '001', YYYY+MM+'01', SUM_TAX_406, SUM_TAX_406, '1', "+
		"'"+account_code+"','"+expense_code+"','"+tax_type_code+"',YYYY,MM,'Absorb Guarantee of Month','ProcessGuarantee' "+
		"FROM SUMMARY_GUARANTEE WHERE HOSPITAL_CODE = '"+this.hospital+"' AND " +
    	"YYYY = '"+this.year+"' AND MM = '"+this.month+"' AND SUM_TAX_406 > 0";
    	if(this.write_comment){
        	System.out.println(s);    		
    	}
    	try{
    		cdb.insert(s);
    		cdb.commitDB();
    	}catch (Exception e){
    		System.out.println(e);
    		cdb.rollDB();
    	}
    	return true;
    }
    private boolean insertExpenseGuaranteeHP(String expense_code, String account_code, String tax_type_code){
    	String s = "INSERT INTO TRN_EXPENSE_DETAIL (" +
		"HOSPITAL_CODE,DOCTOR_CODE,DOC_NO," +
		"LINE_NO,DOC_DATE,AMOUNT,TAX_AMOUNT,EXPENSE_SIGN," +
		"EXPENSE_ACCOUNT_CODE,EXPENSE_CODE,TAX_TYPE_CODE," +
		"YYYY,MM,NOTE,USER_ID) " +
		"SELECT HOSPITAL_CODE, DOCTOR_CODE, " +
		"'Absorb Guarantee', '001', YYYY+MM+'01', SUM_TAX_402, SUM_TAX_402, '1', "+
		"'"+account_code+"','"+expense_code+"','"+tax_type_code+"',YYYY,MM,'Absorb Guarantee of Month','ProcessGuarantee' "+
		"FROM SUMMARY_GUARANTEE WHERE HOSPITAL_CODE = '"+this.hospital+"' AND " +
    	"YYYY = '"+this.year+"' AND MM = '"+this.month+"' AND SUM_TAX_402 > 0";
    	if(this.write_comment){
        	System.out.println(s);    		
    	}
    	try{
    		cdb.insert(s);
    		cdb.commitDB();
    	}catch (Exception e){
    		System.out.println(e);
    		cdb.rollDB();
    	}
    	return true;
    }
}